# This is a sample Python script.
from bs4 import BeautifulSoup
import requests
# Press ⌃R to execute it or replace it with your code.
# Press Double ⇧ to search everywhere for classes, files, tool windows, actions, and settings.
from create_table import engine,Futu_Token,futu_rank_list
# 2. 创建查询窗口 - 必须是选中数据的查询窗口
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.mysql import insert
from datetime import datetime,timedelta
import json
import hmac
import hashlib
from time import localtime, strftime
import logging


#富途美股排行榜
def get_futu_token(page_index):
    str = "{\"marketType\":\"2\",\"plateType\":\"1\",\"rankType\":\"1\",\"page\":\"%d\",\"pageSize\":\"50\"}" % page_index
    str = str.encode('utf-8')
    print(str)
    digest = hmac.new(b"quote_web", str, digestmod=hashlib.sha512).digest().hex()[:10]
    digest=digest.encode('utf-8')
    #print(digest)
    # 取前10位再来一次sha256 再取前10为
    sha256 = hashlib.sha256()
    sha256.update(digest)
    token = sha256.hexdigest()[:10]
    print(token)
    return token


# 创建一个 logger
logger = logging.getLogger('my_logger')
logger.setLevel(logging.DEBUG)  # 设置日志级别
 
# 创建一个 handler，用于写入日志文件
fh = logging.FileHandler('testpy.log')
fh.setLevel(logging.DEBUG)
 
# 创建一个 handler，用于输出到控制台
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)
 
# 定义 handler 的输出格式
formatter = logging.Formatter('%(asctime)s - %(name)s - %(levelname)s - %(message)s')
fh.setFormatter(formatter)
ch.setFormatter(formatter)
 
# 给 logger 添加 handler
logger.addHandler(fh)
logger.addHandler(ch)



# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    logger.debug('main 1')
    Session = sessionmaker(bind=engine)
    # 打开查询窗口
    db_session = Session()

    pageCnt =203
    insertCount =0
    for i in range(0,pageCnt):
        url = 'https://www.futunn.com/quote-api/quote-v2/get-stock-list?marketType=2&plateType=1&rankType=1&page={}&pageSize=50'.format(i)
        print("req", url)
        # 请求头
        token = '{}'.format(get_futu_token(i))

        headers = {
            'Quote-token':token,
            'Accept': 'application/json, text/plain, */*',
            'User-Agent': 'Mozilla/5.0',
            # 可以继续添加其他需要的头信息
        }
        rsp = requests.get(url, headers=headers)

        if rsp.status_code != 200:
            print("err:",rsp.status_code, "msg:",rsp.reason)
            continue

        body = rsp.text
       # print("body", body)
        #logger.debug("body",body)
        json_data= json.loads(body)
        code = json_data['code']
        if code != 0 :
            msg = json_data['message']
            print("err code:",code, "msg:",msg)

        #pageCnt = json_data['data']['pagination']['pageCount']

        #本地时间
        time_delta = timedelta(days=-1)
        now = datetime.now()
        usa_date = now + time_delta
        date = usa_date.strftime("%Y-%m-%d")



        stock_list=json_data['data']['list'] #类型为list 列表中的数据项可以是不同的数据类型，如整型、字符、字典、列表等 【1，'abc', [{12,34}, 'test'】
       # print(type(json_data).__name__, type(stock_list).__name__)
        for s_info in stock_list:
            s_id= s_info['stockId']
            s_name = s_info["name"]
            s_code = s_info['stockCode'] + ".US" #股票代码
            s_changeRatio = s_info['changeRatio'] #"+31.13%", #涨跌幅
            s_priceDirect = s_info['priceDirect'] # "up",
            s_change = s_info['change'] #": "+0.3300", #涨跌额
            s_priceNominal = s_info['priceNominal'] #": "1.3900", #最新价
            s_tradeTrunover = s_info['tradeTrunover'] #": "182.14万", #成交额
            s_tradeVolumn = s_info['tradeVolumn'] #'": "133.20万", #成交量
            s_marketVal = s_info['marketVal'] #": "538.34万", #总市值
            s_circulationMarketValue = s_info['circulationMarketValue'] #": "477.85万", #流通市值
            s_totalShares = s_info['totalShares']#": "387.30万", #总股本
            s_circulationTotalShares= s_info['circulationTotalShares']#": "343.77万", #流通股本
            s_c_5Days= s_info['c_5Days']#": "+16.81%", #5日涨跌幅
            s_c_5Days_priceDirect= s_info['c_5Days_priceDirect']#": "up",
            s_c_10Days= s_info['c_10Days']#": "+42.95%", #10日涨跌幅
            s_c_10Days_priceDirect= s_info['c_10Days_priceDirect']#": "up",
            s_c_20Days= s_info['c_20Days']#": "+51.52%", #20日涨跌幅
            s_c_20Days_priceDirect= s_info['c_20Days_priceDirect']#": "up",
            s_c_60Days= s_info['c_60Days']#": "-17.26%", #60日涨跌幅
            s_c_60Days_priceDirect= s_info['c_60Days_priceDirect']#": "down",
            s_c_120Days= s_info['c_120Days']#": "-11.79%",
            s_c_120Days_priceDirect= s_info['c_120Days_priceDirect']#": "down",
            s_c_250Days= s_info['c_250Days']#": "-79.03%",
            s_c_250Days_priceDirect= s_info['c_250Days_priceDirect']#": "down",
            s_c_YearDays= s_info['c_YearDays']#": "+19.83%", #
            s_c_YearDays_priceDirect= s_info['c_YearDays_priceDirect']#": "up",
            s_trustAum= s_info['trustAum']#": "0.00",
            s_leverage= s_info['leverage']#": null,
            s_tradeChangeRatio= s_info['tradeChangeraio']#": "38.75%", #换手率
            s_priceAmplitude= s_info['priceAmplitude']#": "36.79%",
            s_volumnRatio= s_info['volumnRatio']#": "1.33",
            s_buysellRatio= s_info['buysellRatio']#": "-54.10%"

            insert_stmt = insert(futu_rank_list).values(code=s_code,
                                                   futu_id=s_id,
                                                   name = s_name,
                                                   date=date,
                                                   marketVal=s_marketVal,
                                                   priceNominal= s_priceNominal,
                                                   circulationMarketValue = s_circulationMarketValue,
                                                   totalShares = s_totalShares,
                                                   circulationShares = s_circulationTotalShares,
                                                   day5= s_c_5Days,
                                                   day10 = s_c_10Days,
                                                   day20 = s_c_20Days,
                                                   day60 = s_c_60Days,
                                                   day120 = s_c_120Days,
                                                   day250 = s_c_250Days,
                                                   year = s_c_YearDays,
                                                   trustAum = s_trustAum,
                                                   leverage = s_leverage,
                                                   tradeChangeRatio = s_tradeChangeRatio,
                                                   volumnRatio =s_volumnRatio,
                                                   buysellRatio =s_buysellRatio,
                                                   changeRatio= s_changeRatio,
                                                   tradeTrunover = s_tradeTrunover,
                                                   tradeVolumn = s_tradeVolumn,
                                                   priceAmplitude = s_priceAmplitude
            )

            sql =insert_stmt.on_duplicate_key_update(update_ts=datetime.now())
            #logger.debug(sql)
            try:
                cnt = db_session.execute(sql)
                if not cnt:
                    logger.error(sql)

                db_session.commit()
                insertCount+=1
            except Exception as e:
                logger.error(e)
            

            #print("id:",s_id , "name:",s_name, "code:",s_code)
            #print(s_info)

        print("insert count:",insertCount)










# See PyCharm help at https://www.jetbrains.com/help/pycharm/
